﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Fuyao.Business.Entities.Models;
using System.Data.Common;

namespace Fuyao.Data.SQLServer.DAO
{
    public class DAOVendedor
    {
        string strCnx = clsConexionDB.getConexionSQL();

        public bool InsertVendedor(Vendedor BE)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("INSERT INTO dbo.Vendedor (NombreVendedor, ApellidoVendedor,CreadoPor,FechaCreacion, Estado) ");
                strCommand.Append("VALUES (@NombreVendedor,@ApellidoVendedor,@CreadoPor,getDate(),1)");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@NombreVendedor", SqlDbType.VarChar, 40).Value = BE.NombreVendedor;
                sqlCommand.Parameters.Add("@ApellidoVendedor", SqlDbType.VarChar, 50).Value = BE.ApellidoVendedor;
                sqlCommand.Parameters.Add("@CreadoPor", SqlDbType.Int).Value = BE.CreadoPor;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public bool UpdateVendedor(Vendedor BE)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("UPDATE dbo.Vendedor set NombreVendedor=@NombreVendedor, ApellidoVendedor=@ApellidoVendedor, Estado=@Estado,FechaModificacion=getDate(),ModificadoPor=@ModificadoPor  ");
                strCommand.Append("WHERE VendedorID = @VendedorID ");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@VendedorID", SqlDbType.Int).Value = BE.VendedorID;
                sqlCommand.Parameters.Add("@NombreVendedor", SqlDbType.VarChar, 40).Value = BE.NombreVendedor;
                sqlCommand.Parameters.Add("@ApellidoVendedor", SqlDbType.VarChar, 50).Value = BE.ApellidoVendedor;         
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = BE.Estado;
                sqlCommand.Parameters.Add("@ModificadoPor", SqlDbType.Int).Value = BE.CreadoPor;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public bool DeleteVendedor(int idVend, int idUserLogueado)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("UPDATE dbo.Vendedor set Estado=@Estado,FechaModificacion=getDate(),ModificadoPor=@ModificadoPor  ");
                strCommand.Append("WHERE VendedorID = @VendedorID ");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@VendedorID", SqlDbType.Int).Value = idVend;
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = false;
                sqlCommand.Parameters.Add("@ModificadoPor", SqlDbType.Int).Value = idUserLogueado;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public List<Vendedor> getVendedorByFilter(Vendedor BE)
        {

            Vendedor oVendedor = null;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);
            List<Vendedor> listVendedor = new List<Vendedor>();

            try
            {
                strCommand.Append("FYAO_getVendedorByFiler");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                sqlCommand.Parameters.Add("@NombreVendedor", SqlDbType.VarChar, 40).Value = BE.NombreVendedor;
                sqlCommand.Parameters.Add("@ApellidoVendedor", SqlDbType.VarChar, 50).Value = BE.ApellidoVendedor;
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = BE.Estado;

                cnx.Open();
                SqlDataReader dr = sqlCommand.ExecuteReader();

                while (dr.Read())
                {
                    oVendedor = new Vendedor();
                    if (!Convert.IsDBNull(dr["VendedorID"])) oVendedor.VendedorID = Convert.ToInt32(dr["VendedorID"]);
                    if (!Convert.IsDBNull(dr["NombreVendedor"])) oVendedor.NombreVendedor = Convert.ToString(dr["NombreVendedor"]);
                    if (!Convert.IsDBNull(dr["ApellidoVendedor"])) oVendedor.ApellidoVendedor = Convert.ToString(dr["ApellidoVendedor"]);
                    if (!Convert.IsDBNull(dr["Estado"])) oVendedor.Estado = Convert.ToBoolean(dr["Estado"]);
                    listVendedor.Add(oVendedor);
                }

                dr.Close();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return listVendedor;
        }
    }
}
